/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.HocSinhPOJO;
import java.sql.ResultSet;
import java.util.ArrayList;


/**
 *
 * @author MinhHieu
 */
public class HocSinhDAO {

    public static ArrayList<HocSinhPOJO> search(String MaHocSinh, String TenHocSinh,String GioiTinh, String NgaySinh, String DiaChi, String Email,boolean type){
        ArrayList<HocSinhPOJO> list = new ArrayList<HocSinhPOJO>();
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();

            String symbol;
            if(type){
                symbol="%";
            }else {
                symbol="";
            }

            String sql = "SELECT * FROM hocsinh WHERE 1 ";
            if(MaHocSinh.isEmpty()==false){
                sql += String.format(" AND MaHocSinh LIKE '%s%s%s'",symbol,MaHocSinh,symbol);
            }
            if(TenHocSinh.isEmpty()==false){
                sql += String.format(" AND TenHocSinh LIKE '%s%s%s'",symbol,TenHocSinh,symbol);
            }
            if(GioiTinh.isEmpty()==false){
                sql += String.format(" AND GioiTinh LIKE '%s'",GioiTinh);
            }
            if(NgaySinh.isEmpty()==false){
                sql += String.format(" AND NgaySinh LIKE '%s'",NgaySinh);
            }
            if(DiaChi.isEmpty()==false){
                sql += String.format(" AND DiaChi LIKE '%s%s%s'",symbol,DiaChi,symbol);
            }
            if(Email.isEmpty()==false){
                sql += String.format(" AND Email LIKE '%s%s%s'",symbol,Email,symbol);
            }
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                HocSinhPOJO hs = new HocSinhPOJO();
                hs.setMaHS(rs.getString("MaHocSinh"));
                hs.setTenHS(rs.getString("TenHocSinh"));
                hs.setGioiTinh(rs.getString("GioiTinh"));
                hs.setNgaySinh(rs.getString("NgaySinh"));
                hs.setDiaChi(rs.getString("DiaChi"));
                hs.setEmail(rs.getString("Email"));
                list.add(hs);
            }
            
            helper.close();
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }

    public static ArrayList<HocSinhPOJO> selectHocSinh_KoLop() {
        ArrayList<HocSinhPOJO> list = new ArrayList<HocSinhPOJO>();
        try {
            String sql = "SELECT * "
                    + " FROM hocsinh hs "
                    + " WHERE NOT EXISTS (SELECT * FROM hocsinh_lop hsl WHERE hs.MaHocSinh = hsl.MaHocSinh)";

            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            //rut trich du lieu
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                HocSinhPOJO hs = new HocSinhPOJO();
                hs.setMaHS(rs.getString("MaHocSinh"));
                hs.setTenHS(rs.getString("TenHocSinh"));
                hs.setGioiTinh(rs.getString("GioiTinh"));
                String[] NgaySinh = rs.getString("NgaySinh").split("-");
                hs.setNgaySinh(NgaySinh[0]);
                hs.setDiaChi(rs.getString("DiaChi"));
                list.add(hs);
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }

    public static ArrayList<HocSinhPOJO> selectHocSinh_Lop(String MaLop) {
        ArrayList<HocSinhPOJO> list = new ArrayList<HocSinhPOJO>();
        try {
            String sql = "SELECT * "
                    + "FROM hocsinh hs JOIN hocsinh_lop hsl ON hs.MaHocSinh=hsl.MaHocSinh "
                    + "WHERE MaLop = '"+MaLop+"'";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            //rut trich du lieu
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                HocSinhPOJO hs = new HocSinhPOJO();
                hs.setMaHS(rs.getString("MaHocSinh"));
                hs.setTenHS(rs.getString("TenHocSinh"));
                hs.setGioiTinh(rs.getString("GioiTinh"));
                String[] NgaySinh = rs.getString("NgaySinh").split("-");
                hs.setNgaySinh(NgaySinh[0]);
                hs.setDiaChi(rs.getString("DiaChi"));
                list.add(hs);
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }

    public static ArrayList<HocSinhPOJO> layhocsinh() {
        ArrayList<HocSinhPOJO> list = new ArrayList<HocSinhPOJO>();
        try {
            String sql = "SELECT* FROM HOCSINH";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            //rut trich du lieu
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                HocSinhPOJO hs = new HocSinhPOJO();
                hs.setMaHS(rs.getString("MaHocSinh"));
                hs.setTenHS(rs.getString("TenHocSinh"));
                hs.setNgaySinh(rs.getString("NgaySinh"));
                hs.setGioiTinh(rs.getString("GioiTinh"));
                hs.setDiaChi(rs.getString("DiaChi"));
                hs.setEmail(rs.getString("Email"));
                list.add(hs);
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }

    public static boolean ThemHocSinh(String maHS,String tenHS,String ngaysinh,String gioitinh,String diachi,String email) {
        boolean kq = false;
        try {
            String sql = String.format("INSERT INTO HocSinh(MaHocSinh,TenHocSinh,NgaySinh,GioiTinh,DiaChi,Email) VALUES('%s','%s','%s','%s','%s','%s')",maHS,tenHS,ngaysinh,gioitinh,diachi,email);
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            int a = helper.excecuteUpdate(sql);
            if(a==1){
                kq = true;
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return kq;
    }

   
    
    public static boolean  XoaHS(String mahs) {
        boolean kq = false;
        try {

            String sql = String.format("DELETE FROM HOCSINH WHERE MaHocSinh = '%s'",mahs);
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            int a = helper.excecuteUpdate(sql);
            if(a==1){
                kq = true;
            }
            helper.close();
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return kq;
    }
     public static boolean  suaHs(String mHS, String tenHS,String ns,String gioitinh,String dc,String email) {
        boolean kq = false;
        try {
            String sql = String.format("UPDATE HOCSINH SET TenHocSinh='%s',NgaySinh='%s', GioiTinh='%s',DiaChi='%s' ,Email='%s' WHERE MaHocSinh='%s'",tenHS,ns,gioitinh,dc,email,mHS);
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            int a = helper.excecuteUpdate(sql);
            if(a==1){
                kq = true;
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return kq;
    }
}
